package com.small.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.small.commom.Common;

/**
 * SQLiteOpenHelper是一个辅助类，用来管理数据库的创建和版本他，它提供两个方面的功能
 * 第一，getReadableDatabase()、getWritableDatabase
 * ()可以获得SQLiteDatabase对象，通过该对象可以对数据库进行操作
 * 第二，提供了onCreate()、onUpgrade()两个回调函数，允许我们再创建和升级数据库时，进行自己的操作
 */
public class DbHelper extends SQLiteOpenHelper {

	public static SQLiteDatabase mDb;
//	private Context ctx;
	public String TB_PARENT_SQ = "Table_Parent_SQ";
	public String TB_CHILD_SQ = "Table_Child_SQ";
	public String TB_PARENT_SHOP = "Table_Parent_Shop";
	public String TB_CHILD_SHOP = "Table_Child_Shop";
	// 商圈表创建
	public String CREATE_TABLE_CHILDSQ = "CREATE TABLE IF NOT EXISTS "
			+ TB_CHILD_SQ
			+ " (_id INTEGER PRIMARY KEY AUTOINCREMENT,PCategoryID integer,PCategoryName varchar(20),PLetter varchar(10),CategoryID integer,CategoryName varchar(20),Letter varchar(10))";
	// 商圈表创建
	public String CREATE_TABLE_PARENTSQ = "CREATE TABLE IF NOT EXISTS "
			+ TB_PARENT_SQ
			+ " (_id INTEGER PRIMARY KEY AUTOINCREMENT,PCategoryID integer,PCategoryName varchar(20),PLetter varchar(10))";
	// 商圈类别二级表创建
	public String CREATE_TABLE_CHILDSHOP = "CREATE TABLE IF NOT EXISTS "
			+ TB_CHILD_SHOP
			+ " (_id INTEGER PRIMARY KEY AUTOINCREMENT,PCategoryID integer,PCategoryName varchar(20),PLetter varchar(10),CategoryID integer,CategoryName varchar(20),Letter varchar(10))";
	// 商圈表一级表创建
	public String CREATE_TABLE_PARENTSHOP = "CREATE TABLE IF NOT EXISTS "
			+ TB_PARENT_SHOP
			+ " (_id INTEGER PRIMARY KEY AUTOINCREMENT,PCategoryID integer,PCategoryName varchar(20),PLetter varchar(10))";

	/**
	 * 在SQLiteOpenHelper的子类当中，必须有该构造函数
	 * 
	 * @param context
	 *            上下文对象
	 * @param name
	 *            数据库名称
	 * @param factory
	 * @param version
	 *            当前数据库的版本，值必须是整数并且是递增的状态
	 */
	public DbHelper(Context context, String name, CursorFactory factory,
			int version) {
		super(context, name, factory, version);
	}

	public DbHelper(Context context) {
		super(context, Common.DB_NAME, null, Common.DB_VERSION);
//		ctx = context;
	}

	public void openDataBase() throws SQLException {
		mDb = getWritableDatabase();
//				openOrCreateDatabase(Common.DB_NAME, 0, null);
		mDb.execSQL(CREATE_TABLE_CHILDSQ);
		mDb.execSQL(CREATE_TABLE_PARENTSQ);
		mDb.execSQL(CREATE_TABLE_CHILDSHOP);
		mDb.execSQL(CREATE_TABLE_PARENTSHOP);
	}

	public SQLiteDatabase getDb() {
		return mDb;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		System.out.println("create a database");
	}

	@Override
	public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
		System.out.println("upgrade a database");
	}

	public static final String KEY_PCategoryID = "PCategoryID";
	public static final String KEY_PCategoryName = "PCategoryName";
	public static final String KEY_PLetter = "PLetter";
	public static final String KEY_CategoryID = "CategoryID";
	public static final String KEY_CategoryName = "CategoryName";
	public static final String KEY_Letter = "Letter";
	String[] strColsParent = new String[] { KEY_PCategoryID, KEY_PCategoryName,
			KEY_PLetter };
	String[] strColsChild = new String[] { KEY_PCategoryID, KEY_PCategoryName,
			KEY_PLetter, KEY_CategoryID, KEY_CategoryName, KEY_Letter };

	// 插入一级级商圈信息
	public void insertParentSQInfo(int PCategoryID, String PCategoryName,
			String PLetter) {

		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_PCategoryID, PCategoryID);
		initialValues.put(KEY_PCategoryName, PCategoryName);
		initialValues.put(KEY_PLetter, PLetter);
		if (!checkSQParentKey(PCategoryID, TB_PARENT_SQ))  
		mDb.insert(TB_PARENT_SQ, null, initialValues);
		 
	}

	

	@Override
	public synchronized void close() {
		super.close();
		if (mDb != null)
		mDb.close();
	}

	// 插入二级商圈信息
	public void insertChildSQInfo(int PCategoryID, String PCategoryName,
			String PLetter, int CategoryID, String CategoryName, String Letter) {

		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_PCategoryID, PCategoryID);
		initialValues.put(KEY_PCategoryName, PCategoryName);
		initialValues.put(KEY_PLetter, PLetter);
		initialValues.put(KEY_CategoryID, CategoryID);
		initialValues.put(KEY_CategoryName, CategoryName);
		initialValues.put(KEY_Letter, Letter);
		if (!checkSQChildKey(PCategoryID, CategoryID, TB_CHILD_SQ))  
			  mDb.insert(TB_CHILD_SQ, null, initialValues);		 
	} 
	// 插入一级级商圈信息
	public void insertParentShopInfo(int PCategoryID, String PCategoryName,
			String PLetter) {

		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_PCategoryID, PCategoryID);
		initialValues.put(KEY_PCategoryName, PCategoryName);
		initialValues.put(KEY_PLetter, PLetter);
		if (!checkSQParentKey(PCategoryID, TB_PARENT_SHOP))  
			  mDb.insert(TB_PARENT_SHOP, null, initialValues);
		 
	}

	// 插入二级商圈信息
	public void insertChildShopInfo(int PCategoryID, String PCategoryName,
			String PLetter, int CategoryID, String CategoryName, String Letter) {

		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_PCategoryID, PCategoryID);
		initialValues.put(KEY_PCategoryName, PCategoryName);
		initialValues.put(KEY_PLetter, PLetter);
		initialValues.put(KEY_CategoryID, CategoryID);
		initialValues.put(KEY_CategoryName, CategoryName);
		initialValues.put(KEY_Letter, Letter);
		if (!checkSQChildKey(PCategoryID, CategoryID, TB_CHILD_SHOP))  
			  mDb.insert(TB_CHILD_SHOP, null, initialValues);
		 
	}
	
	public boolean checkSQChildKey(int PCategoryID, int CategoryID, String table) {
		if (mDb==null) {
			mDb = getReadableDatabase();
		}
		Cursor c = mDb.query(table, strColsChild, KEY_PCategoryID + "="
				+ PCategoryID + " and " + KEY_CategoryID + "=" + CategoryID
				, null, null, null, null); 
		boolean rc = c.getCount() > 0;
		
//		c.deactivate();
		return rc;
	}
	public boolean checkSQParentKey(int PCategoryID, String table) {
		if (mDb==null) {
			mDb = getReadableDatabase();
		}
		Cursor c = mDb.query(table, strColsParent, KEY_PCategoryID + "="
				+ PCategoryID , null, null, null, null);
		Log.e("e", ""+ c.getCount());
		boolean rc = c.getCount() > 0;
//		c.deactivate();
		return rc;
	}
	public Cursor getParentSQInfo() {		
		return getParentAllCursor(TB_PARENT_SQ);
	}
	public Cursor getChildSQInfo(int id) {		
		return getChildrenCursor(id,TB_CHILD_SQ);
	}
	public Cursor getParentShopInfo() {		
		return getParentAllCursor(TB_PARENT_SHOP);
	}
	public Cursor getChildShopInfo(int id) {	
		return getChildrenCursor(id,TB_CHILD_SHOP); 
	}

	public Cursor getParentAllCursor(String table){
		if (mDb==null) {
			mDb = getReadableDatabase();
		}	 
		 Cursor c= mDb.query(table, strColsParent, null, null, null, null, null);		 
		 return c;
	}
	public Cursor getChildrenAllCursor(String table){
		if (mDb==null) {
			mDb = getReadableDatabase();
		}	 
		 Cursor c= mDb.query(table, strColsChild, null, null, null, null, null); 
		 return c;
	}
	
	public Cursor getChildrenCursor(int PCategoryID,String table){
		if (mDb==null) {
			mDb = getReadableDatabase();
		}	 
		 Cursor c= mDb.query(table, strColsChild,  KEY_PCategoryID + "="
					+ PCategoryID , null, null, null, null);		 
		 return c;
	}
}
